﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Text.RegularExpressions;
using System.Data;
using BooksStore;
using System.Data.Linq;
using System.Xml;
using System.Globalization;
using System.Data.Sql;
using System.Data.SqlClient;

class Books
{
    public static string ValidateIsbn(string isbn)
    {
        string newIsbn = isbn.Replace("-", string.Empty).Trim();
        if (newIsbn.Length == 13)
        {
            for (int i = 0; i < newIsbn.Length; i++)
            {
                if (!Char.IsDigit(newIsbn[i]))
                {
                    return null;
                }
            }
            return newIsbn;
        }
        else
        {
            return null;
        }
    }

    public static void ParseBooks(string fileName)
    {
        try
        {
            TextReader reader = new StreamReader(fileName, Encoding.UTF8);
            using (reader)
            {
                reader.ReadLine();
                string line = "";
                while ((line = reader.ReadLine()) != null) 
                {
                    string bookSplitPattern = @"[\|]";
                    string[] book = Regex.Split(line, bookSplitPattern, RegexOptions.IgnorePatternWhitespace);
                    string bookTitle = book[0].Trim();
                    string authorSplitPattern = @"[,|;]";
                    string[] authors = Regex.Split(book[1], authorSplitPattern, RegexOptions.IgnorePatternWhitespace);
                    string isbn = ValidateIsbn(book[2]);
                    decimal price = decimal.Parse(book[3],CultureInfo.InvariantCulture);
                    int amount = int.Parse(book[4]);
                    string webSite = "";
                    if (book[5].Trim() != String.Empty)
                    {
                        webSite = book[5].Trim();
                    }
                    else
                    {
                        webSite = null;
                    }
                    InsertBook(bookTitle, authors, isbn, price, amount, webSite);
                }
            }
        }
        catch (FileNotFoundException e)
        {
            
            throw new FileNotFoundException(e.FileName);
        }
        
    }

    public static void InsertBook(string title, string[] authors, string isbn, decimal price, int amount, string webSite)
    {
        if (isbn == null)
        {
            Console.WriteLine("Book \"{0}\" has not valid isbn!", title);
            return;
        }
        else
        {
            DataClassesBookstoreDataContext context = new DataClassesBookstoreDataContext();
            using (context)
            {
                var books =
                    from b in context.Books
                    where b.Isbn == isbn
                    select b;

                if (books.Count() == 0)
                {
                    Book newBook = new Book()
                    {
                        Title = title,
                        Isbn = isbn,
                        Price = price,
                        Amount = amount,
                        WebSite = webSite
                    };

                    context.Books.InsertOnSubmit(newBook);
                    context.SubmitChanges();
                    Console.WriteLine("Book inserted!");
                    InsertAuthors(authors, context);
                    int bookId = newBook.BookID;
                    InsertBooksAuhotrsRelations(bookId, authors, context);
                }
                else
                {
                    Console.WriteLine("Book with this isbn - {0} already exists and will be updated!", isbn);
                    Book currentBook =
                        (from book in context.Books
                         where book.Isbn == isbn
                         select book).First();

                    currentBook.Title = title;
                    currentBook.Price = price;
                    currentBook.Amount += amount;
                    currentBook.WebSite = webSite;

                    var bookAuthorRelations =
                        from bookAuhor in context.BooksAuthors
                        where bookAuhor.BookID == currentBook.BookID
                        select bookAuhor;

                    context.BooksAuthors.DeleteAllOnSubmit(bookAuthorRelations);
                    InsertAuthors(authors, context);
                    InsertBooksAuhotrsRelations(currentBook.BookID, authors, context);

                    context.SubmitChanges();
                }
            }
        }
    }

    public static void InsertBooksAuhotrsRelations(int bookId, string[] authors, DataClassesBookstoreDataContext context)
    {
        int[] authorsIds = GetAutohrsIDs(authors, context);
        for (int i = 0; i < authorsIds.Length; i++)
        {
            BooksAuthor newBooksAuthors = new BooksAuthor()
            {
                BookID = bookId,
                AuthorID = authorsIds[i]
            };
            context.BooksAuthors.InsertOnSubmit(newBooksAuthors);
            context.SubmitChanges();
        }
    }

    public static void InsertAuthors(string[] authors, DataClassesBookstoreDataContext context)
    {
        for (int i = 0; i < authors.Length; i++)
        {
            var authorInDataBase =
                from author in context.Authors
                where author.Name == authors[i].Trim()
                select author.Name;

            if (authorInDataBase.Count() == 0)
            {
                Author newAuthor = new Author() { Name = authors[i].Trim() };
                context.Authors.InsertOnSubmit(newAuthor);
                context.SubmitChanges();
                Console.WriteLine("Author inserted");                
            }
        }
    }

    public static int[] GetAutohrsIDs(string[] authors, DataClassesBookstoreDataContext context)
    {
        List<int> result = new List<int>();
        for (int i = 0; i < authors.Length; i++)
        {
            int authorId =
                (from author in context.Authors
                 where author.Name == authors[i].Trim()
                 select author.AuthorId).First();
            result.Add(authorId);
        }
        return result.ToArray();
    }

    public static void ImportImages(string dirName)
    {
        DirectoryInfo dir = new DirectoryInfo(dirName);
        if (!dir.Exists)
        {
            Console.WriteLine("No such directory: {0}", dir.FullName);
            return;
        }
        else
        {
            Console.WriteLine("Start importing images...");
            DataClassesBookstoreDataContext context = new DataClassesBookstoreDataContext();
            using (context)
            {
                foreach (FileInfo file in dir.GetFiles())
                {
                    if (file.Extension == ".jpg")
                    {
                        int pointPosition = file.Name.IndexOf(".");
                        string isbn = file.Name.Substring(0, pointPosition);
                        if (ValidateIsbn(isbn) != null)
                        {
                            Book book =
                                (from b in context.Books
                                 where b.Isbn == isbn
                                 select b).SingleOrDefault();

                            if (book.Isbn == isbn)
                            {
                                byte[] fileContent = ReadImage(file.FullName);

                                var coverInDataBase =
                                    from cover in context.Covers
                                    where cover.BookID == book.BookID
                                    select cover;

                                if (coverInDataBase.Count() == 0)
                                {
                                    Cover newCover = new Cover()
                                    {
                                        Image = fileContent,
                                        BookID = book.BookID
                                    };

                                    context.Covers.InsertOnSubmit(newCover);
                                    context.SubmitChanges();
                                    Console.WriteLine("Image imported.");
                                }
                                else
                                {
                                    coverInDataBase.First().Image = fileContent;
                                    context.SubmitChanges();
                                    Console.WriteLine("Image updated.");
                                }                                
                                
                            }
                            else
                            {
                                Console.WriteLine("Book wiht ISBN = {0} doesn't exist",isbn);
                            }
                        }
                        else
                        {
                            Console.WriteLine("File: {0} has not valid ISBN", file.Name);
                        }
                    }
                }
            }            
        }
    }

    public static byte[] ReadImage(string fileName)
    {
        using (FileStream reader = new FileStream(fileName, FileMode.Open))
        {
            byte[] result = new byte[reader.Length];
            int offset = 0;
            while (true)
            {
                int bytesRead = reader.Read(result, offset, (int)reader.Length - offset);
                if (bytesRead == 0)
                {
                    break;
                }
                offset += bytesRead;
            }
            return result;
        }        
    }

    public static void InsertStoreSalesRelations(int storeId, List<int> sales, DataClassesBookstoreDataContext context)
    {
        foreach (int saleId in sales)
        {
            StoreSale storeSale = new StoreSale()
            {
                SaleID = saleId,
                StoreID = storeId,
            };
            context.StoreSales.InsertOnSubmit(storeSale);
        }
        context.SubmitChanges();
    }

    public static void ParseSales(string fileName)
    {
        if (!File.Exists(fileName))
        {
            throw new FileNotFoundException("File not found!");
        }

        DataClassesBookstoreDataContext context = new DataClassesBookstoreDataContext();
        using (context)
        {
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load(fileName);
            XmlNode root = xmlDoc.DocumentElement;
            DateTime date = DateTime.ParseExact(root.Attributes["date"].Value, "dd/MM/yyyy", CultureInfo.InvariantCulture);
        
            foreach (XmlNode store in root.ChildNodes)
            {
                List<int> sales = new List<int>();
                string storeName = store.Attributes["store"].Value;
                foreach (XmlNode sale in store.ChildNodes)
                {
                    string isbnBeforeVal = sale["isbn"].InnerText;
                    string isbn = ValidateIsbn(isbnBeforeVal);
                    int amount = int.Parse(sale["amount"].InnerText);

                    var isbns =
                        from b in context.Books
                        where b.Isbn == isbn
                        select b;

                    if (isbns.Count() > 0)
                    {
                        Sale newSale = new Sale()
                        {
                            Date = date,
                            Isbn = isbn,
                            Amount = amount
                        };

                        context.Sales.InsertOnSubmit(newSale);
                        context.SubmitChanges();
                        Console.WriteLine("Sale added!");
                        sales.Add(newSale.SaleID);
                    }
                    else
                    {
                        Console.WriteLine("WARNING:Book with isbn {0} is missing!", isbnBeforeVal);
                    }
                }

                var stores =
                    from s in context.Stores
                    where s.Name == storeName
                    select s;
                
                if (stores.Count() > 0)
                {
                    int storeId = stores.First().StoreID;
                    InsertStoreSalesRelations(storeId, sales, context);
                }
                else
                {
                    Store newStore = new Store() 
                    {
                        Name = storeName
                    };

                    context.Stores.InsertOnSubmit(newStore);
                    context.SubmitChanges();
                    Console.WriteLine("Store: \"{0}\" added!",storeName);

                    InsertStoreSalesRelations(newStore.StoreID, sales, context);
                }
            }
        }
    }

    public static List<BookSale> GetSales(DateTime dateStart, DateTime dateEnd, DataClassesBookstoreDataContext context)
    {
        List<BookSale> result = new List<BookSale>();
        var salesInRange = context.ExecuteQuery<BookSale>(
            "select s.Isbn as Isbn, sum(s.Amount) as Amount, SUM(s.Amount*b.Price) as Income from Sales s " +
            "join Books b ON b.Isbn = s.Isbn " +
            "where s.Date between {0} and {1} "+
            "group by s.Isbn",dateStart,dateEnd);


        //Dictionary<string, int> booksSold = new Dictionary<string, int>();

        //foreach (var item in salesInRange)
        //{
        //    if (booksSold.ContainsKey(item.Isbn))
        //    {
        //        booksSold[item.Isbn] += item.Amount;
        //    }
        //    else
        //    {
        //        booksSold.Add(item.Isbn, item.Amount);
        //    }
        //}

        //foreach (KeyValuePair<string, int> item in booksSold)
        //{
        //    BookSale sale = new BookSale();
        //    sale.Amount = item.Value;
        //    sale.Isbn = item.Key;
        //    decimal price =
        //        (from book in context.Books
        //         where book.Isbn == item.Key
        //         select book.Price).First();

        //    sale.Income = item.Value * price;
        //    result.Add(sale);
        //}
        result = salesInRange.ToList();
        return result;
    }

    public static void WriteBookSale(XmlTextWriter writer, BookSale bookSale)
    {
        writer.WriteStartElement("book-sale");
        
        writer.WriteStartElement("isbn");
        writer.WriteValue(bookSale.Isbn);
        writer.WriteEndElement();

        writer.WriteStartElement("amount");
        writer.WriteValue(bookSale.Amount);
        writer.WriteEndElement();

        writer.WriteStartElement("incomes");
        writer.WriteValue(String.Format("{0:0.00}",bookSale.Income));
        writer.WriteEndElement();

        writer.WriteEndElement();
    }

    public static void CreateReport()
    {
        DateTime date = DateTime.Now;
        DateTime dateStart = new DateTime(date.Year, date.Month, 1);
        DateTime dateEnd = new DateTime(date.Year, date.Month + 1, 1);
        dateEnd  = dateEnd.AddDays( -1);
        DataClassesBookstoreDataContext context = new DataClassesBookstoreDataContext();
        List<BookSale> sales = new List<BookSale>();
        using (context)
        {
            sales = GetSales(dateStart, dateEnd, context);            
        }

        using (XmlTextWriter writer = new XmlTextWriter("../../report.xml", Encoding.UTF8))
        {
            writer.Formatting = Formatting.Indented;
            writer.IndentChar = '\t';
            writer.Indentation = 1;
            writer.WriteStartDocument();
            writer.WriteStartElement("sales-report");
            writer.WriteAttributeString("start-date", String.Format("{0}/{1}/{2}",
                dateStart.Day, dateStart.Month, dateStart.Year));
            writer.WriteAttributeString("end-date", String.Format("{0}/{1}/{2}",
                dateEnd.Day, dateEnd.Month, dateEnd.Year));

            foreach (var item in sales)
            {
                WriteBookSale(writer, item);
            }

            writer.WriteStartElement("total-incomes");
            decimal total = sales.Sum(s => s.Income);
            writer.WriteValue(String.Format("{0:0.00}",total));
            writer.WriteEndDocument();
        }
    }

    static void Main()
    {
        //ParseBooks(@"../../books.txt");
        //ImportImages(@"../../images");
        //ParseSales("../../sales.xml");
        CreateReport();
    }
}
